package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.util.ArrayList;

import util.TimeZoneUtil;
import bean.OSBean;
import bean.trocaOsBean;

public class OSDao {
	
	/*
	 * cadastrarOsServlet
	 */
	public void inserir(Connection conn, OSBean OS) throws SQLException {

		String sql = "insert into os(" +
				"idcliente, " +
				"idempresa, " +
				"dataentrada, " +
				"descricao," +
				"comentario," +
				"situacao," +
				"urgente," +
				"valor," +
				"prazoentrega" +
				") " +
				"values(" +
				"?," +
				"?," +
				"?," +
				"?," +
				"?," +
				"?," +
				"?," +
				"?," +
				"?" +
				")";

		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, OS.getIdCliente());
		ps.setInt(2, OS.getIdEmpresa());
		ps.setTimestamp(3, OS.getDataEntrada());
		ps.setString(4, OS.getDescricao());
		ps.setString(5, OS.getComentario());
		ps.setString(6, OS.getSituacao());
		ps.setDouble(7, OS.getUrgente());
		ps.setDouble(8, OS.getValor());
		ps.setTimestamp(9, OS.getPrazoEntrega());
		ps.execute();
		ps.close();
		
	}
	
	/*
	 * enviaOsDesignerServlet
	 */
	public void enviaOSDesigner(Connection conn, OSBean OS) throws SQLException {

		String sql = "update os set " +
				"dataentradesigner = ?, " +
				"situacao = ? " +
				"where id = ?";

		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setTimestamp(1, OS.getDataDesigner());
		ps.setString(2, "designer");
		ps.setDouble(3, OS.getId());
		ps.execute();
		ps.close();
	}
	
	/*
	 * enviaOsDesignerServlet
	 */
	public boolean isOSDesigner(Connection conn, OSBean OS) throws SQLException {

		String sql = "select * from os, designer where situacao in ('designer', 'voltouDesigner') and os.id = ? and designer.idos = os.id";
		
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setDouble(1, OS.getId());
				
		ResultSet rs = ps.executeQuery();
		if(rs.next()){
			return true;
		}
		ps.close();
		rs.close();
		
		return false;
	}
	
	/*
	 * trocarOsDesignerServlet
	 */
	public void trocarDesigner(Connection conn, trocaOsBean os, Integer idSecretarioAtual, Integer idDesignerAtual) throws SQLException, ParseException{
		String sql = "insert into trocaos(motivo, idos, datatroca, secanterior, secatual, desianterior, desiatual) values(?,?,?,?,?,?,?)";
		
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1, os.getMotivo());
		ps.setInt(2, os.getIdos());
		ps.setTimestamp(3, os.getDataTroca());
		ps.setInt(4, os.getSecretarioAnterior());
		ps.setInt(5, os.getSecretarioAtual());
		ps.setInt(6, os.getDesignerAnterior());
		ps.setInt(7, os.getDesignerAtual());
		ps.execute();
		ps.close();
		
		String sqldesigner = "update designer set iddesigner = ? where idos = ?";
		PreparedStatement psdes = conn.prepareStatement(sqldesigner);
		psdes.setInt(1, idDesignerAtual);
		psdes.setInt(2, os.getIdos());
		psdes.execute();
		psdes.close();
		
//		String sqlsecretario = "update secretario set idsecretario = ? where idos = ?";
//		PreparedStatement pssec = conn.prepareStatement(sqlsecretario);
//		pssec.setInt(1, idSecretarioAtual);
//		pssec.setInt(2, os.getIdos());
//		pssec.execute();
//		pssec.close();
		
		String sqlos = "update os set dataentradesigner = ? where id = ?";
		PreparedStatement psos = conn.prepareStatement(sqlos);
		psos.setTimestamp(1, new TimeZoneUtil().getHoraLocal());
		psos.setInt(2, os.getIdos());
		psos.execute();
		psos.close();
	}
	
	/*
	 * enviaAprovacaoServlet
	 * proximaSituacaoServlet
	 */
	public void enviaOSAprovar(Connection conn, OSBean OS) throws SQLException {

		String sql = "update os set " +
				"dataaprovar = ?, " +
				"situacao = ? " +
				"where id = ?";

		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setTimestamp(1, OS.getDataAprovar());
		ps.setString(2, "aprovar");
		ps.setDouble(3, OS.getId());
		ps.execute();
		ps.close();
	}

	/*
	 * voltarOsServlet
	 */
	public void voltarOsDesigner(Connection conn, OSBean OS) throws SQLException {

		String sql = "update os set " +
				     "situacao = ?, " +
				     "dataentradesigner = ?, " +
				     "dataaprovar = ? " +
					 "where id = ?";

		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1, "voltouDesigner");
		ps.setTimestamp(2, OS.getDataDesigner());
		ps.setTimestamp(3, null);
		ps.setDouble(4, OS.getId());
		ps.execute();
		ps.close();
	}
	
	/*
	 * enviarImprimirServlet
	 */
	public void enviaOSImprimir(Connection conn, OSBean OS) throws SQLException {

		String sql = "update os set " +
				"dataaprovado = ?, " +
				"dataentraimprimir = ?, " +
				"situacao = ? " +
				"where id = ?";

		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setTimestamp(1, OS.getDataAprovado());
		ps.setTimestamp(2, OS.getDataImprimir());
		ps.setString(3, "imprimir");
		ps.setDouble(4, OS.getId());
		ps.execute();
		ps.close();
	}
	
	/*
	 * proximaSituacaoServlet
	 */
	public void enviaOSProducao(Connection conn, OSBean OS) throws SQLException {

		String sql = "update os set " +
				"dataentraproducao = ?, " +
				"situacao = ? " +
				"where id = ?";

		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setTimestamp(1, OS.getDataProducao());
		ps.setString(2, "producao");
		ps.setDouble(3, OS.getId());
		ps.execute();
		ps.close();
	}
	
	/*
	 * proximaSituacaoServlet
	 */
	public void enviaOSEntrega(Connection conn, OSBean OS) throws SQLException {

		String sql = "update os set " +
				"dataentraentrega = ?, " +
				"situacao = ? " +
				"where id = ?";

		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setTimestamp(1, OS.getDataEntrega());
		ps.setString(2, "entregar");
		ps.setInt(3, OS.getId());
		ps.execute();
		ps.close();
	}
	
	/*
	 * proximaSituacaoServlet
	 */
	public void finalizarOs(Connection conn, OSBean OS) throws SQLException {

		String sql = "update os set " +
				"ativo = ?, " +
				"datafinalizado = ?, " +
				"entregue = ?, " +
				"situacao = ? " +
				"where id = ?";

		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, OS.getAtivo());
		ps.setTimestamp(2, OS.getDataFinalizado());
		ps.setString(3, OS.getEntregue());
		ps.setString(4, "finalizado");
		ps.setInt(5, OS.getId());
		ps.execute();
		ps.close();
	}

	/*
	 * alterarOsServlet
	 */
	public void alterar(Connection conn, OSBean OS) throws SQLException {

		PreparedStatement ps = null;
		if(OS.getDescricao().equals("")){
			String sql = "update os set " +
					"idcliente = ?, " +
					"urgente = ?," +
					"valor = ?," +
					"prazoentrega = ?" +
					" where id = ?";

			ps = conn.prepareStatement(sql);
			ps.setInt(1, OS.getIdCliente());
			ps.setInt(2, OS.getUrgente());
			ps.setDouble(3, OS.getValor());
			ps.setTimestamp(4, OS.getPrazoEntrega());
			ps.setInt(5, OS.getId());
		}else{
			String sql = "update os set " +
			"idcliente = ?, " +
			"descricao = ?," +
			"urgente = ?," +
			"valor = ?," +
			"prazoentrega = ?" +
			" where id = ?";

			ps = conn.prepareStatement(sql);
			ps.setInt(1, OS.getIdCliente());
			ps.setString(2, OS.getDescricao());
			ps.setInt(3, OS.getUrgente());
			ps.setDouble(4, OS.getValor());
			ps.setTimestamp(5, OS.getPrazoEntrega());
			ps.setInt(6, OS.getId());
		}
			ps.execute();
			ps.close();
	}
	
	/*
	 * comentarServlet
	 */
	public void alterarComentario(Connection conn, OSBean OS) throws SQLException {
		
		String sql = "update os set comentario = ? where id = ?";
		
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1, OS.getComentario());
		ps.setInt(2, OS.getId());
		ps.execute();
		ps.close();
	}

	/*
	 * removerOsServlet
	 */
	public void remover(Connection conn, OSBean OS) throws SQLException {

		String sql = "delete from os where id = ?";

		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, OS.getId());
		ps.execute();
		ps.close();
	}

	/*
	 * sem uso
	 */
	public ArrayList<OSBean> getList(Connection conn) throws SQLException {

		String sql = "select * from os";

		PreparedStatement ps = conn.prepareStatement(sql);

		ResultSet rs = ps.executeQuery();
		ArrayList<OSBean> OSs = new ArrayList<OSBean>();
		while (rs.next()) {
			OSBean OS = new OSBean();
			OS.setId(rs.getInt("id"));
			OS.setIdCliente(rs.getInt("idcliente"));
			OS.setDataEntrada(rs.getTimestamp("dataentrada"));
			OS.setDataFinalizado(rs.getTimestamp("datafinalizado"));
			OS.setDataDesigner(rs.getTimestamp("dataentradesigner"));
			OS.setDataAprovar(rs.getTimestamp("dataaprovar"));
			OS.setDataAprovado(rs.getTimestamp("dataaprovado"));
			OS.setDataImprimir(rs.getTimestamp("dataentraimprimir"));
			OS.setDataEntrega(rs.getTimestamp("dataentraentrega"));
			OS.setDescricao(rs.getString("descricao"));
			OS.setComentario(rs.getString("comentario"));
			OS.setSituacao(rs.getString("situacao"));
			OS.setValor(rs.getDouble("valor"));
			
			OSs.add(OS);
		}
		ps.close();
		rs.close();

		return OSs;

	}
	/**
	 * @param conn
	 * @return ArrayList<OSBean>
	 * @throws SQLException
	 * @dependencias tabelaOsGeral.jsp
	 */
	public ArrayList<OSBean> getAll(Connection conn, OSBean os) throws SQLException {

		String sql = "	select 											"+
					 "		os.id as idos,								"+
					 "		os.valor,									"+
					 "		os.dataentrada,								"+
					 "		os.datafinalizado,							"+
					 "		cl.nome as nomecliente,						"+
					 "		(select 									"+
					 "			fu.nome									"+
					 "		from										"+
					 "			funcionario fu,							"+
					 "			secretario se							"+
					 "		where										"+
					 "			fu.id = se.idsecretario					"+
			 		 "		and											"+
			 		 "			se.idos = os.id) as nomesecretario,		"+
					 "		(select										"+
					 "			fu.nome									"+
					 "		from										"+
					 "			funcionario fu,							"+
					 "			designer de								"+
					 "		where										"+
					 "			fu.id = de.iddesigner					"+
					 "		and											"+
					 "			de.idos = os.id) as nomedesigner		"+
					 "	from											"+
					 "		os os,										"+
					 " 		cliente cl									"+
					 "	where											"+
					 "		os.idcliente = cl.id						"+
					 "	and												" +
					 "		os.idempresa = ?							";

		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, os.getIdEmpresa());

		ResultSet rs = ps.executeQuery();
		ArrayList<OSBean> OSs = new ArrayList<OSBean>();
		while (rs.next()) {
			OSBean OS = new OSBean();
			OS.setId(rs.getInt("idos"));
			OS.setDataEntrada(rs.getTimestamp("dataentrada"));
			OS.setDataFinalizado(rs.getTimestamp("datafinalizado"));
			OS.setValor(rs.getDouble("valor"));
			OS.setNomeCliente(rs.getString("nomecliente"));
			OS.setNomeSecretario(rs.getString("nomesecretario"));
			OS.setNomeDesigner(rs.getString("nomedesigner"));
			
			OSs.add(OS);
		}
		ps.close();
		rs.close();

		return OSs;

	}
	
	/**
	 * @param conn
	 * @return ArrayList<OSBean>
	 * @throws SQLException
	 * @dependencias atualizaTabelaOs.jsp
	 */
	
	public ArrayList<OSBean> getListaNova(Connection conn, OSBean os) throws SQLException {

		String sql = "	select												"+ 
					 "		os.id,											"+ 
					 "		os.dataentrada,									"+ 
					 "		os.prazoentrega,								"+
					 "		os.descricao, 									"+
					 "		os.situacao, 									"+
					 "		os.urgente, 									"+
					 "		os.valor, 										"+
					 "		os.idcliente,									"+
					 "		cliente.nome as nomeCliente,					"+
					 "		(select											"+
					 "			fu.nome										"+
					 "		from											"+
					 "			funcionario fu,								"+
					 "			designer de									"+
					 "		where											"+
					 "			fu.id = de.iddesigner						"+
					 "		and												"+
					 "			de.idos = os.id) as nomedesigner			"+	
					 "	from 												"+
					 "		os,												"+ 
					 "		cliente											"+ 
					 "	where 												"+
					 "		os.ativo = '1'									"+ 
					 "	and 												"+
					 "		os.idcliente = cliente.id						"+
					 "	and 												"+
					 "		os.idempresa = ?								"+
					 "	order by 											"+
					 "		os.urgente desc, os.id							";

		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, os.getIdEmpresa());

		ResultSet rs = ps.executeQuery();
		ArrayList<OSBean> OSs = new ArrayList<OSBean>();
		while (rs.next()) {
			OSBean OS = new OSBean();
			OS.setId(rs.getInt("id"));
			OS.setNomeDesigner(rs.getString("nomedesigner"));
			OS.setIdCliente(rs.getInt("idcliente"));
			OS.setNomeCliente(rs.getString("nomeCliente"));
			OS.setDataEntrada(rs.getTimestamp("dataentrada"));
			OS.setPrazoEntrega(rs.getTimestamp("prazoentrega"));
			OS.setDescricao(rs.getString("descricao"));
			OS.setSituacao(rs.getString("situacao"));
			OS.setUrgente(rs.getInt("urgente"));
			OS.setValor(rs.getDouble("valor"));
			
			OSs.add(OS);
		}
		ps.close();
		rs.close();

		return OSs;

	}
	/**	
	 * @param conn
	 * @param situacao
	 * @param id
	 * @return ArrayList<OSBean>
	 * @throws SQLException
	 * @dependencias atualizaTabelaDesigner.jsp
	 */
	public ArrayList<OSBean> getListaOS
	(Connection conn, String situacao, Integer id) throws SQLException {

		String sql ="	select 														"+
					"		os.id as idos,											"+
					"		os.idcliente,											"+
					"		cl.nome as nomecliente,									"+
					"		de.iddesigner,											"+
					"		os.dataentrada,											"+
					"		os.dataentradesigner,									"+
					"		os.dataaprovar,											"+
					"		os.dataaprovado,										"+
					"		os.dataentraimprimir,									"+
					"		os.dataentraentrega,									"+
					"		os.descricao,											"+
					"		os.comentario,											"+
					"		os.situacao,											"+
					"		os.valor,												"+
					"		os.urgente												"+
					"	from 														"+
					"		os os,													"+ 
					"		cliente cl,												"+ 
					"		designer de												"+
					"	where 														"+
					"		os.idcliente = cl.id									"+ 
					"	and 														"+
					"		de.idos = os.id											"+ 
					"	and 														"+
					"		os.situacao in "+situacao+"								"+ 
					"	and 														"+
					"		ativo='1'												"+ 
					"	and 														"+
					"		de.iddesigner = '"+id+"'								"+ 
					"	order by 													"+
					"		os.urgente desc, os.id									";

		PreparedStatement ps = conn.prepareStatement(sql);

		ResultSet rs = ps.executeQuery();
		ArrayList<OSBean> OSs = new ArrayList<OSBean>();
		while (rs.next()) {
			OSBean OS = new OSBean();
			OS.setId(rs.getInt("idos"));
			OS.setIdCliente(rs.getInt("idcliente"));
			OS.setNomeCliente(rs.getString("nomecliente"));
			OS.setIdDesigner(rs.getInt("iddesigner"));
			OS.setDataEntrada(rs.getTimestamp("dataentrada"));
			OS.setDataDesigner(rs.getTimestamp("dataentradesigner"));
			OS.setDataAprovar(rs.getTimestamp("dataaprovar"));
			OS.setDataAprovado(rs.getTimestamp("dataaprovado"));
			OS.setDataImprimir(rs.getTimestamp("dataentraimprimir"));
			OS.setDataEntrega(rs.getTimestamp("dataentraentrega"));
			OS.setDescricao(rs.getString("descricao"));
			OS.setComentario(rs.getString("comentario"));
			OS.setSituacao(rs.getString("situacao"));
			OS.setValor(rs.getDouble("valor"));
			OS.setUrgente(rs.getInt("urgente"));
			
			OSs.add(OS);
		}
		ps.close();
		rs.close();

		return OSs;

	}
	
	/*
	 * atualizaTabelaAprovacao.jsp
	 * atualizaTabelaDesigner.jsp
	 * atualizaTabelaEntrega.jsp
	 * atualizaTabelaImprimir.jsp
	 * atualizaTabelaProducao.jsp
	 */
	public ArrayList<OSBean> getListaSituacaoOS(Connection conn, String situacao, OSBean os) throws SQLException {

		String sql = "select * from os, cliente where os.idcliente = cliente.id and os.situacao = '"+situacao+"' and ativo='1' and os.idempresa = ? order by os.urgente desc, os.id";

		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, os.getIdEmpresa());

		ResultSet rs = ps.executeQuery();
		ArrayList<OSBean> OSs = new ArrayList<OSBean>();
		while (rs.next()) {
			OSBean OS = new OSBean();
			OS.setId(rs.getInt("id"));
			OS.setIdCliente(rs.getInt("idcliente"));
			OS.setNomeCliente(rs.getString("nome"));
			OS.setDataEntrada(rs.getTimestamp("dataentrada"));
			OS.setDataDesigner(rs.getTimestamp("dataentradesigner"));
			OS.setDataAprovar(rs.getTimestamp("dataaprovar"));
			OS.setDataAprovado(rs.getTimestamp("dataaprovado"));
			OS.setDataImprimir(rs.getTimestamp("dataentraimprimir"));
			OS.setDataProducao(rs.getTimestamp("dataentraproducao"));
			OS.setDataEntrega(rs.getTimestamp("dataentraentrega"));
			OS.setDescricao(rs.getString("descricao"));
			OS.setComentario(rs.getString("comentario"));
			OS.setSituacao(rs.getString("situacao"));
			OS.setValor(rs.getDouble("valor"));
			OS.setUrgente(rs.getInt("urgente"));
			
			OSs.add(OS);
		}
		ps.close();
		rs.close();

		return OSs;

	}

	public OSBean getOsDesigner(Connection conn, OSBean OSBean)
			throws SQLException {

		String sql = "select * from os, funcionario, designer where os.id = ? and designer.iddesigner = funcionario.id and os.id = designer.idos";

		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, OSBean.getId());

		ResultSet rs = ps.executeQuery();

		OSBean OS = new OSBean();
		if(rs.next()){
			OS.setId(rs.getInt("id"));
			OS.setIdCliente(rs.getInt("idcliente"));
			OS.setIdDesigner(rs.getInt("iddesigner"));
			OS.setNomeDesigner(rs.getString("nome"));
			OS.setDataEntrada(rs.getTimestamp("dataentrada"));
			OS.setDataDesigner(rs.getTimestamp("dataentradesigner"));
			OS.setDataAprovar(rs.getTimestamp("dataaprovar"));
			OS.setDataAprovado(rs.getTimestamp("dataaprovado"));
			OS.setDataImprimir(rs.getTimestamp("dataentraimprimir"));
			OS.setDataEntrega(rs.getTimestamp("dataentraentrega"));
			OS.setDescricao(rs.getString("descricao"));
			OS.setSituacao(rs.getString("situacao"));
			OS.setValor(rs.getDouble("valor"));
		}
		ps.close();
		rs.close();

		return OS;

	}
	/**
	 * @param conn
	 * @param OSBean
	 * @return OSBean
	 * @throws SQLException
	 * @dependencias alterarOS.jsp; comentarServlet.java; descricaoOsServlet.java
	 */
	public OSBean getOS(Connection conn, OSBean OSBean)
			throws SQLException {

		String sql = "	select 											"+
					 "		os.id as idos,								"+
					 "		os.valor,									"+
					 "		os.dataentrada,								"+
					 "		os.datafinalizado,							"+
					 "		os.dataentradesigner,						"+
					 "		os.dataaprovar,								"+
					 "		os.dataaprovado,							"+
					 "		os.dataentraimprimir,						"+
					 "		os.dataentraentrega,						"+
					 "		os.descricao,								"+
					 "		os.comentario,								"+
					 "		os.situacao,								"+
					 "		os.urgente,									"+
					 "		os.idcliente,								"+
					 "		os.prazoentrega,							"+
					 "		cl.nome as nomecliente,						"+
					 "		(select 									"+
					 "			fu.nome									"+
					 "		from										"+
					 "			funcionario fu,							"+
					 "			secretario se							"+
					 "		where										"+
					 "			fu.id = se.idsecretario					"+
			 		 "		and											"+
			 		 "			se.idos = os.id) as nomesecretario,		"+
					 "		(select										"+
					 "			fu.nome									"+
					 "		from										"+
					 "			funcionario fu,							"+
					 "			designer de								"+
					 "		where										"+
					 "			fu.id = de.iddesigner					"+
					 "		and											"+
					 "			de.idos = os.id) as nomedesigner		"+
					 "	from											"+
					 "		os os,										"+
					 " 		cliente cl									"+
					 "	where											"+
					 "		os.idcliente = cl.id						"+
					 "	and												"+
					 "		os.id = ?									";
		
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, OSBean.getId());
		
		ResultSet rs = ps.executeQuery();
		
		OSBean OS = new OSBean();
		if(rs.next()){
			OS.setId(rs.getInt("idos"));
			OS.setIdCliente(rs.getInt("idcliente"));
			OS.setNomeCliente(rs.getString("nomecliente"));
			OS.setNomeSecretario(rs.getString("nomesecretario"));
			OS.setNomeDesigner(rs.getString("nomedesigner"));
			OS.setDataEntrada(rs.getTimestamp("dataentrada"));
			OS.setDataDesigner(rs.getTimestamp("dataentradesigner"));
			OS.setDataAprovar(rs.getTimestamp("dataaprovar"));
			OS.setDataAprovado(rs.getTimestamp("dataaprovado"));
			OS.setDataImprimir(rs.getTimestamp("dataentraimprimir"));
			OS.setDataEntrega(rs.getTimestamp("dataentraentrega"));
			OS.setDataFinalizado(rs.getTimestamp("datafinalizado"));
			OS.setPrazoEntrega(rs.getTimestamp("prazoentrega"));
			OS.setDescricao(rs.getString("descricao"));
			OS.setComentario(rs.getString("comentario"));
			OS.setSituacao(rs.getString("situacao"));
			OS.setValor(rs.getDouble("valor"));
			OS.setUrgente(rs.getInt("urgente"));
		}
		ps.close();
		rs.close();
		
		return OS;

	}
	
	/*
	 * sem uso
	 */
	public ArrayList<OSBean> getListaOsDat(Connection conn) throws SQLException {

		String sql = "select * from os where os.datafinalizado is not null ";

		PreparedStatement ps = conn.prepareStatement(sql);

		ResultSet rs = ps.executeQuery();
		ArrayList<OSBean> OSs = new ArrayList<OSBean>();
		while (rs.next()) {
			OSBean OS = new OSBean();
			OS.setId(rs.getInt("id"));
			OS.setIdCliente(rs.getInt("idcliente"));
			OS.setIdSecretario(rs.getInt("idsecretario"));
			OS.setIdDesigner(rs.getInt("iddesigner"));
			OS.setDataEntrada(rs.getTimestamp("dataentrada"));
			OS.setDataFinalizado(rs.getTimestamp("datafinalizado"));
			OS.setDataDesigner(rs.getTimestamp("dataentradesigner"));
			OS.setDataAprovar(rs.getTimestamp("dataaprovar"));
			OS.setDataAprovado(rs.getTimestamp("dataaprovado"));
			OS.setDataImprimir(rs.getTimestamp("dataentraimprimir"));
			OS.setDataProducao(rs.getTimestamp("dataentraproducao"));
			OS.setDataEntrega(rs.getTimestamp("dataentraentrega"));
			OS.setDescricao(rs.getString("descricao"));
			OS.setSituacao(rs.getString("situacao"));
			OS.setValor(rs.getDouble("valor"));
			OS.setUrgente(rs.getInt("urgente"));
			
			OSs.add(OS);
		}
		ps.close();
		rs.close();

		return OSs;

	}

	/*
	 * mediaOs
	 */
	public String getMediaTempoOs(Connection conn, Timestamp dataInicial, Timestamp dataFinal, OSBean os) throws SQLException {
		
		String sql = "																									" +
			"SELECT 																									" +
				 "avg(DATE_PART('day', datafinalizado::timestamp - dataentrada::timestamp) * 24 + 						" +
				 "	  DATE_PART('hour', datafinalizado::timestamp - dataentrada::timestamp)) as horas 					" +
			"FROM 																										" +
			     "os			 																						" +
			"where 																										" +
			     "os.datafinalizado <= ? and os.dataentrada >= ?														" +
			     "and os.datafinalizado is not null																		" +
			     "and os.idempresa = ?																					" ;
		
		PreparedStatement ps = conn.prepareStatement(sql);		
		ps.setTimestamp(1, dataFinal);
		ps.setTimestamp(2, dataInicial);
		ps.setInt(3, os.getIdEmpresa());

		ResultSet rs = ps.executeQuery();
		
		String mediaHoras = "nan";
		DecimalFormat df = new DecimalFormat("#,###");

		if(rs.next()){
			if(rs.getString("horas") != null){
				mediaHoras ="Média de "+ df.format(rs.getDouble("horas")) + " horas (Aproximado)";
			}
		}
		
		return mediaHoras;
	}
	
	/*
	 * mediaOs
	 */
	public String getMediaTempoOsDesigner(Connection conn, Timestamp dataInicial, Timestamp dataFinal, Integer id) throws SQLException {
	
	String sql = "SELECT																									" +
				 "      avg(DATE_PART('day', dataaprovar::timestamp - dataentradesigner::timestamp) * 24 + 					" +
				 " 	   		DATE_PART('hour', dataaprovar::timestamp - dataentradesigner::timestamp)) as horas				" +
				 "FROM																										" +
				 "     os, designer 																						" +
				 "where																										" +
				 "     os.dataaprovar <= ? and os.dataentradesigner >= ?													" +
				 "     and designer.iddesigner = ?																			" +
				 "     and os.id = designer.idos																			" +
				 "     and os.dataaprovar is not null																		";
	
	PreparedStatement ps = conn.prepareStatement(sql);
	
	ps.setTimestamp(1, dataFinal);
	ps.setTimestamp(2, dataInicial);
	ps.setInt(3, id);

	ResultSet rs = ps.executeQuery();
	
	String mediaHoras = "nan";
	DecimalFormat df = new DecimalFormat("#,###");

	if(rs.next()){
		if(rs.getString("horas") != null){
			mediaHoras ="Média de "+ df.format(rs.getDouble("horas")) + " horas (Aproximado)";
		}
	}
	
	return mediaHoras;
	}
	
	/*
	 * voltarOsServlet
	 */
	public Integer getTempoOsDesigner(Connection conn, Integer idOs) throws SQLException {
		
		String sql = "SELECT																									" +
					 "      DATE_PART('day', os.dataaprovar::timestamp - os.dataentradesigner::timestamp) * 24 + 				" +
					 "			DATE_PART('hour', os.dataaprovar::timestamp - os.dataentradesigner::timestamp) AS horas			" +
					 "FROM																										" +
					 "		os																									" +
					 "where																										" +
					 "		os.id = ?																							";
		
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, idOs);

		ResultSet rs = ps.executeQuery();
		
		Integer horas = 0;

		if(rs.next()){
			horas = (int) rs.getDouble("horas");
			
		}
		
		return horas;
		}

}

